Skip to main content

Referential Integrity

Referential Integrity ensures that relationships between tables remain consistent and valid.

It is enforced mainly through foreign keys (FKs):

  • A foreign key in one table must reference a valid, existing primary key (PK) in another table.
  • You cannot have “orphan” records (child rows pointing to non-existent parent rows).
  • You cannot delete/update a parent row if child rows still reference it — unless you explicitly define cascade actions.
CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_code VARCHAR(10),
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
);
  • Enrollments.student_id is a foreign key referencing Students.student_id.
  • You cannot insert an enrollment record for a student who doesn’t exist.

Invalid insert (violates referential integrity):

INSERT INTO Enrollments (enrollment_id, student_id, course_code)
VALUES (1, 999, 'CS101'); -- ❌ student_id 999 doesn’t exist

Valid insert:

INSERT INTO Students VALUES (101, 'Alice');
INSERT INTO Enrollments VALUES (1, 101, 'CS101'); -- ✅ works fine

Referential Integrity in Transactions​

Now, let’s connect this with transactions (runtime operations).

When transactions insert, update, or delete rows, referential integrity must still hold true at the end of the transaction (Consistency property of ACID).

CREATE TABLE Accounts (
    account_id INT PRIMARY KEY,
    customer_id INT,
    balance DECIMAL(10,2)
);

CREATE TABLE Transactions (
    txn_id INT PRIMARY KEY,
    account_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);

Case 1: Insert Transaction with Referential Integrity​

BEGIN TRANSACTION;

INSERT INTO Transactions VALUES (2001, 999, 500.00);  -- ❌ fails
-- because account_id 999 does not exist in Accounts

COMMIT;

The transaction fails entirely because referential integrity is violated. This protects the database from “dangling” transactions that point to non-existent accounts.

Case 2: Delete Transaction with Referential Integrity​

If you try:

DELETE FROM accounts WHERE account_id = 101;
  • If Transactions table already has entries referencing account_id = 101, the deletion will be blocked.
  • To allow it, you must define actions:
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
  • ON DELETE CASCADE → deleting an account will also delete its related transactions.
  • ON UPDATE CASCADE → if account_id changes, related transactions update automatically.

Why Referential Integrity Matters​

  • Prevents data anomalies (like enrollments for non-existent students or transactions for deleted accounts).
  • Works with ACID transactions to ensure the Consistency property.
  • Without referential integrity, applications must manually check validity, which is error-prone.